package org.csu.jpetstore.persistence.impl;

import org.csu.jpetstore.domain.Cart;
import org.csu.jpetstore.domain.Item;
import org.csu.jpetstore.persistence.CartDAO;
import org.csu.jpetstore.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class CartDAOImpl implements CartDAO {

    public static final String FIND_CART = "SELECT * FROM cart WHERE status = 0 AND user = ?";
    public static final String INSERT_CART = "INSERT INTO cart (status,user) VALUES (0,?)";
    public static final String ITEM_EXIST = "SELECT number FROM cartitem, cart WHERE itemid = ? AND cart.status = 0 AND cartitem.cart = cart.id AND cart.user = ?";
    public static final String ADD_ITEM = "INSERT INTO cartitem (itemid, number, cart) VALUES (?, ?, ?)";
    public static final String UPDATE_QUANTITIES = "UPDATE cartitem,cart SET cartitem.number = ? WHERE cartitem.itemid = ? AND cart.status = 0 AND cartitem.cart = cart.id AND cart.user = ?";
    public static final String GET_LIST ="SELECT itemid FROM cartitem, cart WHERE cart.status = 0 AND cartitem.cart = cart.id AND cart.user = ?";
    public static final String SUBMIT = "UPDATE cart SET status = 1, orderid = ? WHERE status = 0 AND user = ?";
    public static final String REMOVE = "DELETE cartitem FROM cartitem,cart WHERE cartitem.itemid = ? AND cart.status = 0 AND cartitem.cart = cart.id AND cart.user = ?";
    public static final String FIND_ORDER = "SELECT itemid FROM cartitem, cart WHERE cart.status = 1 AND cartitem.cart = cart.id AND cart.orderid = ?";

    public int findCart(String username) {
        int id = -1;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(FIND_CART);
            preparedStatement.setString(1,username);
            ResultSet resultSet = preparedStatement.executeQuery();

            if(resultSet.next()) {
                id = resultSet.getInt(1);
            }

            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return id;
    }

    public int insertCart(String username) {
       int result = 0;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_CART);
            preparedStatement.setString(1,username);
            result=preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }

       return result;
    }

    @Override
    public int itemNumber(String itemId, String username) {
        int num = 0;

        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(ITEM_EXIST);
            preparedStatement.setString(1, itemId);
            preparedStatement.setString(2,username);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()) {
                num = resultSet.getInt(1);
            }
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return num;
    }

    @Override
    public void remove(Item item, String username) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(REMOVE);
            preparedStatement.setString(1,item.getItemId());
            preparedStatement.setString(2, username);
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void submitCart(String username, int orderid) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(SUBMIT);
            preparedStatement.setInt(1, orderid);
            preparedStatement.setString(2, username);
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<String> findOrder(int orderid) {
        List<String> itemList = new ArrayList<String>();

        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(FIND_ORDER);
            preparedStatement.setInt(1,orderid);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                itemList.add(resultSet.getString(1));
            }
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return itemList;
    }

    @Override
    public void addItemToCart(Item item, String username) {
        if(findCart(username) == -1) {
            insertCart(username);
        }
        int id = findCart(username);

        if(itemNumber(item.getItemId(),username) == 0) {
            try {
                Connection connection = DBUtil.getConnection();
                PreparedStatement preparedStatement = connection.prepareStatement(ADD_ITEM);
                preparedStatement.setString(1, item.getItemId());
                preparedStatement.setInt(2, 1);
                preparedStatement.setInt(3, id);
                preparedStatement.executeUpdate();
                DBUtil.closePreparedStatement(preparedStatement);
                DBUtil.closeConnection(connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        else {
            updateCartQuantities(item,itemNumber(item.getItemId(),username)+1,username);
        }

    }

    @Override
    public void updateCartQuantities(Item item, int num, String username) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_QUANTITIES);
            preparedStatement.setInt(1, num);
            preparedStatement.setString(2, item.getItemId());
            preparedStatement.setString(3, username);
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<String> getItemList(String username) {
        List<String> itemList = new ArrayList<String>();

        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_LIST);
            preparedStatement.setString(1,username);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                itemList.add(resultSet.getString(1));
            }
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return itemList;
    }

}
